﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using PBO.Data;
using PBO.Data.Util;
using PBO.Sql;

namespace PBO.Util
{
    public static class MyBox
    {
        private static readonly DbHelperSQLite db_box = new DbHelperSQLite("Data Source=box.db");

        private static bool HasKey(string key)
        {
            return (int)db_box.GetSingle("select count(*) from pbo_info where id='" + key + "'") > 0;
        }

        public static int Insert(PokemonCustomInfo info)
        {
            string key = info.GetKey();
            if (HasKey(key)) return 0;
            const string sql = "insert into pbo_info (ID,Pid,Ability,Item,Nature,Gender,Lvl,Move1,Move2,Move3,Move4,DvHp,DvAtk,DvDef,DvSpe,DvSpA,DvSpD,EvHp,EvAtk,EvDef,EvSpe,EvSpA,EvSpD) values (@ID,@Pid,@Ability,@Item,@Nature,@Gender,@Lvl,@Move1,@Move2,@Move3,@Move4,@DvHp,@DvAtk,@DvDef,@DvSpe,@DvSpA,@DvSpD,@EvHp,@EvAtk,@EvDef,@EvSpe,@EvSpA,@EvSpD);";
            var dic = ToDic(info);
            return db_box.ExecuteSql(sql, dic);
        }

        internal static void Insert(IEnumerable<PokemonCustomInfo> infos)
        {
            var list = infos.Select(t => ToDic(t));
            const string sql = "insert into pbo_info (ID,Pid,Ability,Item,Nature,Gender,Lvl,Move1,Move2,Move3,Move4,DvHp,DvAtk,DvDef,DvSpe,DvSpA,DvSpD,EvHp,EvAtk,EvDef,EvSpe,EvSpA,EvSpD) values (@ID,@Pid,@Ability,@Item,@Nature,@Gender,@Lvl,@Move1,@Move2,@Move3,@Move4,@DvHp,@DvAtk,@DvDef,@DvSpe,@DvSpA,@DvSpD,@EvHp,@EvAtk,@EvDef,@EvSpe,@EvSpA,@EvSpD);";
            db_box.ExecuteSqlTran(sql, list);
        }

        private static IDictionary<string, object> ToDic(PokemonCustomInfo info)
        {
            var dic = new Dictionary<string, object>();
            dic.Add("Pid", info.Identity);
            dic.Add("ID", info.GetKey());
            dic.Add("Lvl", (int)info.LV);
            dic.Add("Item", (int)info.Item);
            dic.Add("Ability", (int)info.Ability);
            dic.Add("Gender", (int)info.Gender);
            dic.Add("Nature", (int)info.Nature);
            dic.Add("Move1", info.Moves[0]);
            dic.Add("Move2", info.Moves[1]);
            dic.Add("Move3", info.Moves[2]);
            dic.Add("Move4", info.Moves[3]);
            dic.Add("DvHp", info.Iv[0]);
            dic.Add("DvAtk", info.Iv[1]);
            dic.Add("DvDef", info.Iv[2]);
            dic.Add("DvSpe", info.Iv[3]);
            dic.Add("DvSpA", info.Iv[4]);
            dic.Add("DvSpD", info.Iv[5]);
            dic.Add("EvHp", info.Ev[0]);
            dic.Add("EvAtk", info.Ev[1]);
            dic.Add("EvDef", info.Ev[2]);
            dic.Add("EvSpe", info.Ev[3]);
            dic.Add("EvSpA", info.Ev[4]);
            dic.Add("EvSpD", info.Ev[5]);
            return dic;
        }

        public static List<PokemonCustomInfo> GetList(int type1, int type2, int ability, int item, int page_no, int page_size, string order, bool asc)
        {
            var sql = new StringBuilder("select * from v_box where 1=1 ");

            if (ability > 0)
            {
                sql.Append(" and ability=" + ability);
            }
            if (item > 0)
            {
                sql.Append(" and item=" + item);
            }
            if (type1 != 0 || type2 != 0)
            {
                if (type1 == type2)
                {
                    sql.Append(" and type2=0 and type1=" + type1);
                }
                else if (type1 != 0 && type2 != 0)
                {
                    sql.AppendFormat(" and ((type1={0} and type2={1}) or (type1={1} and type2={0}))", type1, type2);
                }
                else
                {
                    sql.AppendFormat(" and (type1={0} or type2={0})", type1 != 0 ? type1 : type2);
                }
            }
            if (!string.IsNullOrEmpty(order))
            {
                sql.Append(" order by " + order);
                if (asc == false) sql.Append(" desc");
            }

            var dt = db_box.ExecuteDataTable(sql.ToString(), page_no, page_size);
            return (from DataRow dr in dt.Rows select GetInfo(dr)).ToList();
        }

        private static PokemonCustomInfo GetInfo(DataRow dr)
        {
            var info = new PokemonCustomInfo();

            info.Identity = dr["Pid"].ToString().ToInt();
            info.NickName = dr["Name"].ToString();
            info.Item = (Item)dr["item"].ToString().ToInt();
            info.Nature = (PokemonNature)dr["Nature"].ToString().ToInt();
            info.Gender = (PokemonGender)dr["Gender"].ToString().ToInt();
            info.Ability = (Trait)dr["Ability"].ToString().ToInt();
            info.LV = (byte)dr["lvl"].ToString().ToInt();

            info.Moves = new[] { dr["move1"].ToString().ToInt(), dr["move2"].ToString().ToInt(), (int)dr["move3"].ToString().ToInt(), dr["move4"].ToString().ToInt() };

            info.Iv.SetValue(new[] 
            { 
                dr["DvHp"].ToString().ToInt(),
                dr["DvAtk"].ToString().ToInt(),
                dr["DvDef"].ToString().ToInt(),
                dr["DvSpe"].ToString().ToInt(),
                dr["DvSpA"].ToString().ToInt(),
                dr["DvSpD"].ToString().ToInt()
            });
            info.Ev.SetValue(new[] 
            { 
                dr["EvHp"].ToString().ToInt(),
                dr["EvAtk"].ToString().ToInt(),
                dr["EvDef"].ToString().ToInt(),
                dr["EvSpe"].ToString().ToInt(),
                dr["EvSpA"].ToString().ToInt(),
                dr["EvSpD"].ToString().ToInt()
            });

            return info;
        }

    }
}
